package sf.database.jdbc.sql;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import sf.common.exception.SmallOrmException;
import sf.common.log.LogContext;
import sf.common.log.OrmLog;
import sf.common.wrapper.Page;
import sf.core.DBCascadeField;
import sf.core.DBField;
import sf.core.DBObject;
import sf.database.dao.DBContext;
import sf.database.dbinfo.DBMetaData;
import sf.database.dbinfo.Feature;
import sf.database.dialect.DBDialect;
import sf.database.dialect.DBProperty;
import sf.database.jdbc.handle.PageListHandler;
import sf.database.jdbc.handle.RowListHandler;
import sf.database.jdbc.handle.SingleRowHandler;
import sf.database.jdbc.rowmapper.MapRowMapper;
import sf.database.jdbc.rowmapper.RowMapper;
import sf.database.jdbc.rowmapper.RowMapperHelp;
import sf.database.jdbc.type.TypeHandler;
import sf.database.listener.EntityListenerManager;
import sf.database.meta.CascadeConfig;
import sf.database.meta.CascadeContext;
import sf.database.meta.CascadeUtils;
import sf.database.meta.ColumnMapping;
import sf.database.meta.MetaHolder;
import sf.database.meta.OptimisticLock;
import sf.database.meta.TableMapping;
import sf.database.support.DBMS;
import sf.database.support.DMLType;
import sf.database.util.DBUtils;
import sf.database.util.OrmUtils;
import sf.database.util.OrmValueUtils;
import sf.database.util.SQLUtils;
import sf.dsl.example.SelectOpt;
import sf.spring.util.Assert;
import sf.spring.util.CollectionUtils;
import sf.tools.JavaTypeUtils;
import sf.tools.NumberUtils;
import sf.tools.StringUtils;

import javax.persistence.GenerationType;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Collections;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;
import java.util.Objects;
import java.util.function.Consumer;
import java.util.stream.Stream;

/**
 * 根据model做的增删查改
 * @author sxf
 */
public class CrudModelImpl implements CrudModelInf {
    private static final Logger logger = LoggerFactory.getLogger(CrudModelImpl.class);

    static ResultSetCallback<List<Map<String, Object>>> mapRsh = new RowListHandler<>(new MapRowMapper());
    private static CrudModelImpl instance = new CrudModelImpl();

    private CrudModelImpl() {

    }

    public static CrudModelImpl getInstance() {
        return instance;
    }

    @Override
    public <T extends DBObject> boolean existsByPrimaryKeys(Connection conn, Class<T> clz, Object... keyParams) {
        TableMapping table = MetaHolder.getMeta(clz);
        List<ColumnMapping> cmList = table.getPkFields();
        if (cmList.size() != keyParams.length) {
            throw new SmallOrmException("主键参数不对");
        }
        DBDialect dialect = DBUtils.doGetDialect(conn, false);
        DBContext dbContext = DBUtils.doGetDBContext(conn);
        SQLContext context = ModelSql.forModelExistsByIds(dbContext, table, dialect, keyParams);
        Byte l = selectOne(conn, Byte.class, context);
        return l != null && l > 0;
    }

    @Override
    public <T extends DBObject> boolean exists(Connection conn, T query) {
        DBDialect dialect = DBUtils.doGetDialect(conn, false);
        DBContext dbContext = DBUtils.doGetDBContext(conn);
        SQLContext context = ModelSql.getQuerySelect(dbContext, dialect, query, false, SelectOpt.exists, false);
        String sql = dialect.sqlPageList(new StringBuilder(context.getSql()), 0, 1).toString();
        context.setSql(sql);
        Byte l = selectOne(conn, Byte.class, context);
        return l != null && l > 0;
    }

    @Override
    public <T extends DBObject> T selectByPrimaryKeys(Connection conn, Class<T> clz, Object... keyParams) {
        TableMapping table = MetaHolder.getMeta(clz);
        List<ColumnMapping> cmList = table.getPkFields();
        if (cmList.size() != keyParams.length) {
            throw new SmallOrmException("主键参数不对");
        }
        DBDialect dialect = DBUtils.doGetDialect(conn, false);
        DBContext dbContext = DBUtils.doGetDBContext(conn);
        SQLContext context = ModelSql.forModelSelectByIds(dbContext, table, dialect, keyParams);
        RowMapper<T> rowMapper = RowMapperHelp.getBeanRowMapper(clz, dbContext);
        ResultSetCallback<T> rsh = new SingleRowHandler<>(rowMapper);
        return select(conn, rsh, context);
    }

    public <T> T select(Connection conn, ResultSetCallback<T> rsh, SQLContext context) {
        Assert.notNull(rsh, "rsh is null.");
        Assert.notNull(context, "context is null.");
        if (StringUtils.isBlank(context.getSql())) {
            logger.warn("select sql is null.");
            return null;
        }
        PreparedStatement ps = null;
        ResultSet rs = null;
        T result = null;
        LogContext log = OrmLog.commonLog(null, context.getSql(), context.getValues());
        try {
            ps = conn.prepareStatement(context.getSql());
            CommonSql.fillSQLStatement(ps, context.getParas());
            rs = ps.executeQuery();
            result = rsh.callback(rs);
            OrmLog.resultSqlLog(null, log, result, () -> OrmLog.getAutoCommit(conn));
        } catch (SQLException e) {
            OrmLog.resultSqlLog(null, log, e);
            throw new SmallOrmException(e);
        } finally {
            DBUtils.close(rs);
            DBUtils.close(ps);
        }
        return result;
    }

    @Override
    public List<Map<String, Object>> selectListMap(Connection conn, SQLContext context) {
        return select(conn, mapRsh, context);
    }

    @Override
    public <T> T selectOne(Connection conn, Class<T> beanClass, SQLContext context) {
        Assert.notNull(context, "context is null.");
        Assert.notNull(beanClass, "beanClass is null.");
        DBContext dbContext = DBUtils.doGetDBContext(conn);
        RowMapper<T> rowMapper = RowMapperHelp.getRowMapper(beanClass, dbContext);
        ResultSetCallback<T> rsh = new SingleRowHandler<T>(rowMapper);
        return select(conn, rsh, context);
    }

    @Override
    public <T> List<T> selectList(Connection conn, Class<T> beanClass, SQLContext context) {
        Assert.notNull(context, "context is null.");
        Assert.notNull(beanClass, "beanClass is null.");
        DBContext dbContext = DBUtils.doGetDBContext(conn);
        RowMapper<T> rowMapper = RowMapperHelp.getRowMapper(beanClass, dbContext);
        ResultSetCallback<List<T>> rsh = new RowListHandler<T>(rowMapper);
        return select(conn, rsh, context);
    }

    @Override
    public <T> List<T> selectList(Connection conn, Class<T> beanClass, SQLContext context, long start, int limit) {
        Assert.notNull(context, "context is null.");
        Assert.notNull(beanClass, "beanClass is null.");
        DBDialect dialect = DBUtils.doGetDialect(conn, false);
        String sql = dialect.sqlPageList(new StringBuilder(context.getSql()), start, limit).toString();
        context.setSql(sql);
        DBContext dbContext = DBUtils.doGetDBContext(conn);
        RowMapper<T> rowMapper = RowMapperHelp.getRowMapper(beanClass, dbContext);
        ResultSetCallback<List<T>> rsh = new RowListHandler<T>(rowMapper);
        return select(conn, rsh, context);
    }

    /**
     * @param conn
     * @param start     0开始
     * @param limit     限制多少条数据
     * @param beanClass
     * @return
     */
    @Override
    public <T> Page<T> selectPage(Connection conn, long start, int limit, Class<T> beanClass, SQLContext context) {
        Assert.notNull(beanClass, "beanClass is null.");
        String sql = context.getSql();
        String countSql = context.getCountSql();
        String listSql = context.getListSql();
        if (StringUtils.isBlank(countSql)) {
            countSql = DBUtils.getSqlSelectCount(sql);
        }
        if (StringUtils.isBlank(listSql)) {
            listSql = sql;
        }
        //设置为查询总数的sql
        context.setSql(countSql);
        Long count = selectOne(conn, Long.class, context);
        if (count == null) {
            count = 0L;
        }
        //改为原来的
        context.setSql(sql);
        Page<T> page = new Page<T>(count, limit);
        List<T> items = Collections.emptyList();
        if (count > 0) {
            String pageSql = DBUtils.doGetDialect(conn, false).sqlPageList(new StringBuilder(listSql), start, limit).toString();
            if (StringUtils.isBlank(pageSql)) {
                DBContext dbContext = DBUtils.doGetDBContext(conn);
                RowMapper<T> rowMapper = RowMapperHelp.getRowMapper(beanClass, dbContext);
                PageListHandler<T> rsh = new PageListHandler<T>(rowMapper);
                rsh.setFirstResult((int) start);// 如果不支持分页，那么使用原始的分页方法 ResultSet.absolute(first)
                rsh.setMaxResults(limit);
                context.setSql(listSql);
                items = select(conn, rsh, context);
            } else {
                // 使用数据库自身的分页SQL语句，将直接返回某一个
                context.setSql(pageSql);
                items = selectList(conn, beanClass, context);
            }
        }
        page.setList(items);
        return page;
    }

    @Override
    public <T> Page<T> selectPage(Connection conn, long start, int limit, Class<T> beanClass, SQLContext context, PageStrategy strategy) {
        Assert.notNull(beanClass, "beanClass is null.");
        String sql = context.getSql();
        String countSql = context.getCountSql();
        String listSql = context.getListSql();
        if (StringUtils.isBlank(countSql)) {
            countSql = DBUtils.getSqlSelectCount(sql);
        }
        if (StringUtils.isBlank(listSql)) {
            listSql = sql;
        }
        //设置为查询总数的sql
        context.setSql(countSql);
        Long count = selectOne(conn, Long.class, context);
        if (count == null) {
            count = 0L;
        }
        //改为原来的
        context.setSql(sql);
        Page<T> page = new Page<T>(count, limit);
        List<T> items = Collections.emptyList();
        if (count > 0) {
            switch (strategy) {
                case onlyList://只查询列表,不包含分页参数.
                    String pageSql = DBUtils.doGetDialect(conn, false).sqlPageList(new StringBuilder(listSql), start, limit).toString();
                    if (StringUtils.isNotBlank(pageSql)) {
                        // 使用数据库自身的分页SQL语句，将直接返回某一个
                        context.setSql(pageSql);
                        items = selectList(conn, beanClass, context);
                    } else {
                        throw new SmallOrmException("not find page sql,please check you sql");
                    }
                    break;
                case fake:
                    DBContext dbContext = DBUtils.doGetDBContext(conn);
                    RowMapper<T> rowMapper = RowMapperHelp.getRowMapper(beanClass, dbContext);
                    PageListHandler<T> rsh = new PageListHandler<T>(rowMapper);
                    rsh.setFirstResult((int) start);// 如果不支持分页，那么使用原始的分页方法 ResultSet.absolute(first)
                    rsh.setMaxResults(limit);
                    context.setSql(listSql);
                    items = select(conn, rsh, context);
                    break;
                case hasOffsetLimit:
                    // 查询语句已包含offset,limit 使用数据库自身的分页SQL语句，将直接返回某一个
                    context.setSql(listSql);
                    items = selectList(conn, beanClass, context);
                    break;
                default:
                    break;
            }
        }
        page.setList(items);
        return page;
    }

    @Override
    public <T extends DBObject> Long selectCount(Connection conn, T query) {
        DBDialect dialect = DBUtils.doGetDialect(conn, false);
        DBContext dbContext = DBUtils.doGetDBContext(conn);
        SQLContext select = ModelSql.getQuerySelect(dbContext, dialect, query, false, true);
        return selectOne(conn, Long.class, select);
    }

    @Override
    public <T extends DBObject> T selectOne(Connection conn, T query) {
        DBDialect dialect = DBUtils.doGetDialect(conn, false);
        DBContext dbContext = DBUtils.doGetDBContext(conn);
        SQLContext select = ModelSql.getQuerySelect(dbContext, dialect, query, false, false);
        Class<T> clz = (Class<T>) query.getClass();
        return selectOne(conn, clz, select);
    }

    @Override
    public <T extends DBObject> T selectOneForUpdate(Connection conn, T query) {
        DBDialect dialect = DBUtils.doGetDialect(conn, false);
        DBContext dbContext = DBUtils.doGetDBContext(conn);
        SQLContext select = ModelSql.getQuerySelect(dbContext, dialect, query, true, false);
        Class<T> clz = (Class<T>) query.getClass();
        return selectOne(conn, clz, select);
    }

    @Override
    public <T extends DBObject> List<T> selectList(Connection conn, T query) {
        DBDialect dialect = DBUtils.doGetDialect(conn, false);
        DBContext context = DBUtils.doGetDBContext(conn);
        SQLContext select = ModelSql.getQuerySelect(context, dialect, query, false, false);
        Class<T> clz = (Class<T>) query.getClass();
        return selectList(conn, clz, select);
    }

    @Override
    public <T extends DBObject> List<T> selectList(Connection conn, T query, long start, int limit) {
        DBDialect dialect = DBUtils.doGetDialect(conn, false);
        DBContext context = DBUtils.doGetDBContext(conn);
        SQLContext select = ModelSql.getQuerySelect(context, dialect, query, false, null, false);
        Class<T> clz = (Class<T>) query.getClass();
        return selectList(conn, clz, select, start, limit);
    }

    @Override
    public <T extends DBObject> List<T> selectListForUpdate(Connection conn, T query) {
        DBDialect dialect = DBUtils.doGetDialect(conn, false);
        DBContext context = DBUtils.doGetDBContext(conn);
        SQLContext select = ModelSql.getQuerySelect(context, dialect, query, true, false);
        Class<T> clz = (Class<T>) query.getClass();
        return selectList(conn, clz, select);
    }

    @Override
    public <T extends DBObject> Page<T> selectPage(Connection conn, T query, long start, int limit) {
        DBDialect dialect = DBUtils.doGetDialect(conn, false);
        DBContext context = DBUtils.doGetDBContext(conn);
        SQLContext select = ModelSql.getQuerySelect(context, dialect, query, false, SelectOpt.page, false);
        Class<T> clz = (Class<T>) query.getClass();
        return selectPage(conn, start, limit, clz, select);
    }

    @Override
    public <T extends DBObject> void selectIterator(Connection conn, Consumer<Iterable<T>> ormIt, T query) {
        DBDialect dialect = DBUtils.doGetDialect(conn, false);
        DBContext dbContext = DBUtils.doGetDBContext(conn);
        SQLContext select = ModelSql.getQuerySelect(dbContext, dialect, query, false, false);
        Crud.getInstance().getCrudSql().selectIterator(conn, ormIt, (Class<T>) query.getClass(), false, select);
    }

    @Override
    public <T extends DBObject> void selectIterator(Connection conn, Consumer<Iterable<T>> ormIt, T query, long start, int limit) {
        DBDialect dialect = DBUtils.doGetDialect(conn, false);
        DBContext dbContext = DBUtils.doGetDBContext(conn);
        SQLContext select = ModelSql.getQuerySelect(dbContext, dialect, query, false, false);
        String pageSql = DBUtils.doGetDialect(conn, false).sqlPageList(new StringBuilder(select.getSql()), start, limit).toString();
        select.setSql(pageSql);
        Crud.getInstance().getCrudSql().selectIterator(conn, ormIt, (Class<T>) query.getClass(), false, select);
    }

    @Override
    public <T extends DBObject> void selectStream(Connection conn, Consumer<Stream<T>> ormStream, T query) {
        DBDialect dialect = DBUtils.doGetDialect(conn, false);
        DBContext dbContext = DBUtils.doGetDBContext(conn);
        SQLContext select = ModelSql.getQuerySelect(dbContext, dialect, query, false, false);
        Crud.getInstance().getCrudSql().selectStream(conn, ormStream, (Class<T>) query.getClass(), false, select);
    }

    @Override
    public int insert(Connection conn, DBObject obj) {
        return insert(conn, obj, false, true);
    }

    @Override
    public int insert(Connection conn, DBObject obj, boolean fast, boolean useOptimisticLock) {
        TableMapping table = MetaHolder.getMeta(obj.getClass());
        DBDialect dialect = DBUtils.doGetDialect(conn, false);
        DBContext dbContext = DBUtils.doGetDBContext(conn);
        SQLContext context = ModelSql.model2Save(conn, dbContext, table, dialect, obj, useOptimisticLock);
        int[] r = batch(conn, dialect, table, context.getSql(), Collections.singletonList(context), Collections.singletonList(1),
                Collections.singletonList(obj), DMLType.INSERT, fast, 1);
        int result = 0;
        if (r.length > 0) {
            result = r[0];
        }
        return result;
    }

    @Override
    public int insertCascade(Connection conn, DBObject obj, DBCascadeField... fields) {
        int count = insert(conn, obj);
        insertLinks(conn, obj, fields);
        return count;
    }

    /*
     * Execute sql update
     */
    @Override
    public int update(Connection conn, DBObject obj) {
        //不开启乐观锁校验
        return update(conn, obj, new SQLContext(), false);
    }

    /**
     * @param conn
     * @param obj
     * @param context           不可为null
     * @param useOptimisticLock 是否使用乐观锁
     * @return
     */
    protected int update(Connection conn, DBObject obj, SQLContext context, boolean useOptimisticLock) {
        DBDialect dialect = DBUtils.doGetDialect(conn, false);
        TableMapping table = MetaHolder.getMeta(obj.getClass());
        DBContext dbContext = DBUtils.doGetDBContext(conn);
        EntityListenerManager.runPreUpdate(obj);
        ModelSql.forModelUpdate(dbContext, table, dialect, obj, context, useOptimisticLock, false);
        int result = execute(conn, context);
        obj.clearUpdate();
        EntityListenerManager.runPostUpdate(obj);
        return result;
    }

    @Override
    public <T extends DBObject> int updateAndSet(Connection conn, T obj) {
        SQLContext context = new SQLContext();
        int result = update(conn, obj, context, true);
        if (result > 0) {
            TableMapping tm = MetaHolder.getMeta(obj.getClass());
            Map<DBField, ColumnMapping> versionMap = tm.getVersionMap();
            boolean dateVersion = false;
            for (Map.Entry<DBField, ColumnMapping> entry : versionMap.entrySet()) {
                if (Date.class.isAssignableFrom(entry.getValue().getClz())) {
                    dateVersion = true;
                    break;
                }
            }
            //更新成功,则变更对象的版本值
            if (dateVersion) {
                //对于日期,乐观锁最新的值需要从数据库获取.
                OptimisticLock.setNewOptimisticLockValues(conn, obj);
            } else {
                List<SQLParameter> preResult = context.getPreResultParas();
                if (CollectionUtils.isNotEmpty(preResult)) {
                    for (SQLParameter v : preResult) {
                        OrmValueUtils.setValue(obj, v.getColumnMapping(), v.getValue());
                    }
                }
            }
        }
        return result;
    }

    @Override
    public <T extends DBObject> int updateWithVersion(Connection conn, T obj) {
        return update(conn, obj, new SQLContext(), true);
    }

    @Override
    public int updateCascade(Connection conn, DBObject obj, DBCascadeField... fields) {
        updateLinks(conn, obj, fields);
        return update(conn, obj);
    }

    @Override
    public int merge(Connection conn, DBObject obj) {
        TableMapping table = MetaHolder.getMeta(obj.getClass());
        List<ColumnMapping> cmList = table.getPkFields();
        DBField[] fields = new DBField[cmList.size()];
        boolean exist = true;
        for (int i = 0; i < cmList.size(); i++) {
            fields[i] = cmList.get(i).getField();
        }
        Object[] values = OrmUtils.getDataObjectValues(obj, fields);
        for (int i = 0; i < values.length; i++) {
            if (values[i] == null) {
                exist = false;
                break;
            }
        }
        DBObject temp = null;
        if (exist) {
            temp = selectByPrimaryKeys(conn, obj.getClass(), values);
        }
        int i = 0;
        if (temp == null) {
            i = insert(conn, obj);
        } else {
            //去除相同值
            Map<DBField, Object> updateValueMap = obj.updateValueMap();
            for (Iterator<Map.Entry<DBField, Object>> it = updateValueMap.entrySet().iterator(); it.hasNext(); ) {
                Map.Entry<DBField, Object> entry = it.next();
                Object o = OrmValueUtils.getValue(temp, table.getSchemaMap().get(entry.getKey()));
                if (Objects.equals(o, entry.getValue())) {
                    it.remove();
                }
            }
            //为空无需更新了.
            if (!updateValueMap.isEmpty()) {
                i = update(conn, obj);
            }
        }
        return i;
    }

    @Override
    public <T extends DBObject> int deleteByPrimaryKeys(Connection conn, Class<T> clz, Object... keyParams) {
        return deleteByPrimaryKeys(conn, false, clz, keyParams);
    }

    @Override
    public int delete(Connection conn, DBObject obj) {
        return delete(conn, false, obj);
    }

    @Override
    public <T extends DBObject> int logicDeleteByPrimaryKeys(Connection conn, Class<T> clz, Object... keyParams) {
        return deleteByPrimaryKeys(conn, true, clz, keyParams);
    }

    @Override
    public <T extends DBObject> int deleteByPrimaryKeys(Connection conn, boolean logicDelete, Class<T> clz, Object... keyParams) {
        TableMapping table = MetaHolder.getMeta(clz);
        DBDialect dialect = DBUtils.doGetDialect(conn, false);
        DBContext dbContext = DBUtils.doGetDBContext(conn);
        SQLContext context = null;
        if (logicDelete && table.getLogicDeleteField() != null) {
            DBObject obj = OrmValueUtils.instance(table);
            setLogicDeleteValue(obj, table);
            List<ColumnMapping> pKeys = table.getPkFields();
            for (int i = 0; i < pKeys.size(); i++) {
                obj.prepareUpdate(pKeys.get(i).getField(), keyParams[i]);
            }
            context = new SQLContext();
            ModelSql.forModelUpdate(dbContext, table, dialect, obj, context, false, false);
        } else {
            context = ModelSql.forModelDeleteByIds(dbContext, table, dialect, keyParams);
        }
        return execute(conn, context);
    }

    @Override
    public int logicDelete(Connection conn, DBObject obj) {
        return delete(conn, true, obj);
    }

    @Override
    public int delete(Connection conn, boolean logicDelete, DBObject obj) {
        TableMapping table = MetaHolder.getMeta(obj.getClass());
        DBDialect dialect = DBUtils.doGetDialect(conn, false);
        DBContext dbContext = DBUtils.doGetDBContext(conn);
        EntityListenerManager.runPreRemove(obj);
        SQLContext context = null;
        if (logicDelete && table.getLogicDeleteField() != null) {
            setLogicDeleteValue(obj, table);
            context = new SQLContext();
            ModelSql.forModelUpdate(dbContext, table, dialect, obj, context, false, false);
        } else {
            context = ModelSql.forModelDelete(dbContext, table, dialect, obj);
        }
        int result = execute(conn, context);
        EntityListenerManager.runPostRemove(obj);
        return result;
    }

    public static void setLogicDeleteValue(DBObject obj, TableMapping table) {
        ColumnMapping cm = table.getLogicDeleteField();
        Class<?> ldClass = cm.getClz();
        String logicDeleteValue = cm.getColumnInfo().logicDeletedValue();
        if (StringUtils.isNotBlank(logicDeleteValue)) {
            if (ldClass == Boolean.class || ldClass == Boolean.TYPE) {
                obj.prepareUpdate(cm.getField(), Boolean.valueOf(logicDeleteValue));
            } else if (String.class.isAssignableFrom(ldClass)) {
                obj.prepareUpdate(cm.getField(), logicDeleteValue);
            } else if (ldClass == Integer.class || ldClass == Integer.TYPE) {
                obj.prepareUpdate(cm.getField(), Integer.parseInt(logicDeleteValue));
            }
        }
    }

    /**
     * Execute sql update
     * @param conn
     * @param context
     * @return
     */
    @Override
    public int execute(Connection conn, SQLContext context) {
        Assert.notNull(context, "context is null.");
        if (StringUtils.isBlank(context.getSql())) {
            logger.warn("execute sql is null.");
            return 0;
        }
        LogContext log = OrmLog.commonLog(null, context.getSql(), context.getValues());
        int result = 0;
        PreparedStatement pst = null;
        if (StringUtils.isBlank(context.getSql())) {
            return result;
        }
        try {
            pst = conn.prepareStatement(context.getSql());
            CommonSql.fillSQLStatement(pst, context.getParas());
            result = pst.executeUpdate();
            OrmLog.resultSqlLog(null, log, result, () -> OrmLog.getAutoCommit(conn));
        } catch (Exception e) {
            OrmLog.resultSqlLog(null, log, e);
            throw new SmallOrmException(e);
        } finally {
            DBUtils.close(pst);
        }
        return result;
    }

    @Override
    public int deleteCascade(Connection conn, DBObject obj, DBCascadeField... fields) {
        deleteLinks(conn, obj, fields);
        return delete(conn, obj);
    }

    /**
     * Batch save models using the "insert into ..." sql generated by the model in modelList.
     */
    @Override
    public int[] batchInsert(Connection conn, Collection<? extends DBObject> modelList, boolean insertFast, boolean useOptimisticLock, int batchSize) {
        return batchInsert(conn, modelList, insertFast, useOptimisticLock, batchSize, false);
    }

    public int[] batchInsert(Connection conn, Collection<? extends DBObject> modelList, boolean insertFast, boolean useOptimisticLock, int batchSize, boolean useAll) {
        if (CollectionUtils.isEmpty(modelList)) {
            return new int[0];
        }
        DBObject data = modelList.iterator().next();
        TableMapping table = MetaHolder.getMeta(data.getClass());
        DBDialect dialect = DBUtils.doGetDialect(conn, false);
        DBContext dbContext = DBUtils.doGetDBContext(conn);
        int[] count = null;
        {
            count = new int[modelList.size()];
            Map<String, List<SQLContext>> map1 = new HashMap<>();
            Map<String, List<Integer>> map2 = new HashMap<>();
            Map<String, List<DBObject>> map3 = new HashMap<>();
            for (DBObject object : modelList) {
                SQLContext context = ModelSql.model2Save(conn, dbContext, table, dialect, object, useOptimisticLock);
                List<SQLContext> list1 = map1.computeIfAbsent(context.getSql(), k -> new LinkedList<>());
                list1.add(context);
                List<Integer> list2 = map2.computeIfAbsent(context.getSql(), k -> new LinkedList<>());
                list2.add(1);
                List<DBObject> list3 = map3.computeIfAbsent(context.getSql(), k -> new LinkedList<>());
                list3.add(object);
            }
            int i = 0;
            for (Map.Entry<String, List<SQLContext>> entry : map1.entrySet()) {
                String sql = entry.getKey();
                List<SQLContext> list1 = entry.getValue();
                List<Integer> list2 = map2.get(sql);
                List<DBObject> list3 = map3.get(sql);
                int[] counts = batch(conn, dialect, table, sql, list1, list2, list3, DMLType.INSERT, insertFast, batchSize);
                for (int j = 0; j < counts.length; j++) {
                    count[i++] = counts[j];
                }
            }
        }
        return count;
    }

    /**
     * Batch update models using the attrs names of the model in
     * modelList.
     */
    @Override
    public int[] batchUpdate(Connection conn, Collection<? extends DBObject> modelList, int batchSize) {
        if (CollectionUtils.isEmpty(modelList)) {
            return new int[0];
        }
        DBObject data = modelList.iterator().next();
        TableMapping table = MetaHolder.getMeta(data.getClass());
        DBDialect dialect = DBUtils.doGetDialect(conn, false);
        DBContext dbContext = DBUtils.doGetDBContext(conn);
        int[] count = null;
        if (dialect.getProperty(DBProperty.NVL_FUNCTION) == null) {

        }
        {
            count = new int[modelList.size()];
            //save the context for every model,group by sql
            Map<String, List<SQLContext>> map1 = new HashMap<>();
            Map<String, List<Integer>> map2 = new HashMap<>();
            Map<String, List<DBObject>> map3 = new HashMap<>();
            for (DBObject object : modelList) {
                SQLContext context = ModelSql.forModelUpdate(dbContext, table, dialect, object, new SQLContext(), true, false);
                List<SQLContext> list1 = map1.computeIfAbsent(context.getSql(), k -> new LinkedList<>());
                list1.add(context);
                List<Integer> list2 = map2.computeIfAbsent(context.getSql(), k -> new LinkedList<>());
                list2.add(1);
                List<DBObject> list3 = map3.computeIfAbsent(context.getSql(), k -> new LinkedList<>());
                list3.add(object);
            }
            int i = 0;
            for (Map.Entry<String, List<SQLContext>> entry : map1.entrySet()) {
                String sql = entry.getKey();
                List<SQLContext> list1 = entry.getValue();
                List<Integer> list2 = map2.get(sql);
                List<DBObject> list3 = map3.get(sql);
                int[] counts = batch(conn, dialect, table, sql, list1, list2, list3, DMLType.UPDATE, false, batchSize);
                for (int j = 0; j < counts.length; j++) {
                    count[i++] = counts[j];
                }
            }
        }
        return count;
    }

    /**
     * Batch delete records using the columns names of the record in recordList.
     * @param modelList the table name
     * @param batchSize the primary key of the table, composite primary key is
     *                  separated by comma character: ","
     */
    @Override
    public int[] batchDelete(Connection conn, Collection<? extends DBObject> modelList, int batchSize) {
        if (CollectionUtils.isEmpty(modelList)) {
            return new int[0];
        }
        DBObject data = modelList.iterator().next();
        TableMapping table = MetaHolder.getMeta(data.getClass());
        DBDialect dialect = DBUtils.doGetDialect(conn, false);
        DBContext dbContext = DBUtils.doGetDBContext(conn);
        int[] count = null;
        {
            count = new int[modelList.size()];
            Map<String, List<SQLContext>> map1 = new HashMap<>();
            Map<String, List<Integer>> map2 = new HashMap<>();
            Map<String, List<DBObject>> map3 = new HashMap<>();
            for (DBObject object : modelList) {
                SQLContext context = ModelSql.forModelDelete(dbContext, table, dialect, object);
                List<SQLContext> list1 = map1.computeIfAbsent(context.getSql(), k -> new LinkedList<>());
                list1.add(context);
                List<Integer> list2 = map2.computeIfAbsent(context.getSql(), k -> new LinkedList<>());
                list2.add(1);
                List<DBObject> list3 = map3.computeIfAbsent(context.getSql(), k -> new LinkedList<>());
                list3.add(object);
            }
            int i = 0;
            for (Map.Entry<String, List<SQLContext>> entry : map1.entrySet()) {
                String sql = entry.getKey();
                List<SQLContext> list1 = entry.getValue();
                List<Integer> list2 = map2.get(sql);
                List<DBObject> list3 = map3.get(sql);
                int[] counts = batch(conn, dialect, table, sql, list1, list2, list3, DMLType.DELETE, false, batchSize);
                for (int j = 0; j < counts.length; j++) {
                    count[i++] = counts[j];
                }
            }
        }
        return count;
    }

    @Override
    public int[] batch(Connection conn, DBDialect dialect, TableMapping table, String sql, List<SQLContext> list, List<Integer> dataSizeList, List<DBObject> dataList, DMLType type, boolean insertFast,
                       int batchSize) {
        if (batchSize < 1)
            throw new IllegalArgumentException("The batchSize must more than 0.");

        if (dialect == null) {
            dialect = DBUtils.doGetDialect(conn, false);
        }
        if (list == null || list.size() == 0) {
            return new int[0];
        }
        if (StringUtils.isBlank(sql)) {
            return new int[list.size()];
        }
        boolean isInsert = type == DMLType.INSERT;
        boolean returnKey = false;

        doPreMethod(dataList, type);

        int size = list.size();
        boolean isSingle = size == 1;//是否为单个对象
        int[] result = new int[size];
        List<ColumnMapping> pkFields = table.getPkFields();
        String[] pKeys = null;
        if (CollectionUtils.isNotEmpty(pkFields)) {
            pKeys = new String[pkFields.size()];
            for (int i = 0; i < pkFields.size(); i++) {
                ColumnMapping cm = pkFields.get(i);
                pKeys[i] = cm.getRawColumnName();
            }
        }

        int pointer = 0;
        int counter = 0;
        int dataCount = 0;
        PreparedStatement pst = null;
        boolean generatedKey = false;
        LogContext log = new LogContext();
        log.setStart(System.currentTimeMillis());
        log.setSql(sql);
        try {
            if (isInsert && !insertFast && !dialect.isNosql()) {
                if (CollectionUtils.isNotEmpty(pkFields)) {
                    for (ColumnMapping pkCm : pkFields) {
                        if (pkCm.getGv() != null && (pkCm.getGv().strategy() == GenerationType.AUTO ||
                                pkCm.getGv().strategy() == GenerationType.SEQUENCE || pkCm.getGv().strategy() == GenerationType.IDENTITY) &&
                                JavaTypeUtils.isNumberClass(pkCm.getClz())) {
                            returnKey = true;
                            break;
                        }
                    }
                    //sqlserver需要是额外查询,才能获取主键值.
                    if (returnKey && DBMS.sqlserver.getNumber() != dialect.getNumber()) {
                        if (DBMS.oracle.getNumber() == dialect.getNumber() || DBMS.postgresql.getNumber() == dialect.getNumber()) {
                            pst = conn.prepareStatement(sql, pKeys);
                        } else {
                            pst = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
                            generatedKey = true;
                        }
                    }
                }
            }
            if (pst == null) {
                pst = conn.prepareStatement(sql);
            }
            Iterator<Integer> dataSizeListIt = dataSizeList.iterator();
            Iterator<DBObject> dataListIt = dataList.iterator();
            for (SQLContext sqlContext : list) {
                List<Object> values = new ArrayList<>();
                setJdbcValues(pst, sqlContext, values);
                //批量处理
                log = OrmLog.batchCommonLog(null, log, sql, !isSingle, batchSize, ++counter, values);
                dataCount = dataCount + dataSizeListIt.next();
                if (!isSingle) {
                    pst.addBatch();
                    if (dataCount >= batchSize) {
                        counter = 0;
                        int[] r = pst.executeBatch();
                        pst.clearBatch();
                        for (int i = 0; i < r.length; i++) {
                            result[pointer++] = r[i];
                        }
                        if (returnKey && !insertFast) {
                            setPkValueAfter(dialect, pst, generatedKey, dataCount, dataListIt, table);
                        }
                        dataCount = 0;
                    }
                }
            }
            if (isSingle) {
                result[0] = pst.executeUpdate();
                if (returnKey && !insertFast) {
                    setPkValueAfter(dialect, pst, generatedKey, dataCount, dataListIt, table);
                }
            } else {
                //批量处理
                if (dataCount > 0) {
                    int[] r = pst.executeBatch();
                    pst.clearBatch();
                    for (int i = 0; i < r.length; i++) {
                        result[pointer++] = r[i];
                    }
                    if (returnKey && !insertFast) {
                        setPkValueAfter(dialect, pst, generatedKey, dataCount, dataListIt, table);
                    }
                }
            }

            OrmLog.resultSqlLog(null, log, result, () -> OrmLog.getAutoCommit(conn));
        } catch (Exception e) {
            OrmLog.resultSqlLog(null, log, e);
            throw new SmallOrmException(e);
        } finally {
            DBUtils.close(pst);
        }

        for (DBObject data : dataList) {
            data.clearUpdate();
        }

        doPostMethod(dataList, type);
        return result;
    }

    private static void setJdbcValues(PreparedStatement pst, SQLContext sqlContext, List<Object> values) throws SQLException {
        int j = 0;
        //记录日志
        List<SQLParameter> columns = sqlContext.getParas();
        for (SQLParameter p : columns) {
            Object value = p.getValue();//此处不使用p.getValue()是为了兼容list列表
            ColumnMapping column = p.getColumnMapping();
            column.getHandler().set(pst, value, ++j);
            values.add(value);
        }
    }

    public static <T extends DBObject> void doPreMethod(Collection<T> list, DMLType type) {
        if (type != null) {
            switch (type) {
                case INSERT:
                    for (DBObject data : list) {
                        EntityListenerManager.runPrePersist(data);
                    }
                    break;
                case UPDATE:
                    for (DBObject data : list) {
                        EntityListenerManager.runPreUpdate(data);
                    }
                    break;
                case DELETE:
                    for (DBObject data : list) {
                        EntityListenerManager.runPreRemove(data);
                    }
                    break;
                default:
                    break;
            }
        }
    }

    public static <T extends DBObject> void doPostMethod(Collection<T> list, DMLType type) {
        if (type != null) {//实体监听器
            switch (type) {
                case INSERT:
                    for (DBObject data : list) {
                        EntityListenerManager.runPostPersist(data);
                    }
                    break;
                case UPDATE:
                    for (DBObject data : list) {
                        EntityListenerManager.runPostUpdate(data);
                    }
                    break;
                case DELETE:
                    for (DBObject data : list) {
                        EntityListenerManager.runPostRemove(data);
                    }
                    break;
                default:
                    break;
            }
        }
    }

    /**
     * Get id after save record.
     * @param dialect
     * @param pst
     * @param generatedKey
     * @param total
     * @param it
     * @param table
     */
    private void setPkValueAfter(DBDialect dialect, PreparedStatement pst, boolean generatedKey, int total, Iterator<? extends DBObject> it, TableMapping table) {
        if (dialect.has(Feature.BATCH_GENERATED_KEY_ONLY_LAST)) {
            //sqlite,h2,derby走这个模式.
            setPkValueAfterGuess(pst, total, it, table);
            return;
        } else if (dialect.has(Feature.BATCH_GENERATED_KEY_BY_FUNCTION)) {
            //sqlserver 需要特殊处理
            setPkValueAfterFunction(dialect.getProperty(DBProperty.GET_IDENTITY_FUNCTION), pst, total, it, table);
            return;
        }
        ResultSet rs = null;
        try {
            rs = pst.getGeneratedKeys();
            if (rs != null) {
                ResultSetMetaData rsmd = rs.getMetaData();
                int cols = rsmd.getColumnCount();
                while (rs.next()) {
                    if (it.hasNext()) {
                        DBObject o = it.next();
                        for (int i = 1; i <= cols; i++) {
                            String columnName = rsmd.getColumnLabel(i);
                            if (columnName == null || columnName.length() == 0) {
                                columnName = rsmd.getColumnName(i);
                            }
                            ColumnMapping cm = null;
                            if (generatedKey) {
                                cm = table.getPkFields().get(0);
                            } else {
                                cm = SQLUtils.getColumnByDBName(table, columnName);
                            }
                            if (cm != null) {
                                TypeHandler<?> type = cm.getHandler();
                                Object val = type.get(rs, i);
                                OrmValueUtils.setValue(o, cm, val);
                            }
                        }
                    }
                }
            }
        } catch (Exception e) {
            throw new SmallOrmException(e);
        } finally {
            DBUtils.closeQuietly(rs);
        }
    }

    /**
     * 猜测主键
     * @param pst
     * @param size
     * @param it
     * @param table
     */
    private void setPkValueAfterGuess(PreparedStatement pst, int size, Iterator<? extends DBObject> it, TableMapping table) {
        ResultSet rs = null;
        try {
            rs = pst.getGeneratedKeys();
            if (rs != null) {
                Assert.isTrue(rs.next(), "The JDBC Driver may not support getGeneratedKeys() operation.");
                long max = rs.getLong(1);
                int i = 0;
                while (it.hasNext()) {
                    DBObject o = it.next();
                    ColumnMapping cm = table.getPkFields().get(0);
                    long num = max - (size - (++i));
                    OrmValueUtils.setValue(o, cm, NumberUtils.getTargetNumber(num, cm.getClz()));
                    if (i == size) {
                        break;
                    }
                }
            }
        } catch (Exception e) {
            throw new SmallOrmException(e);
        } finally {
            DBUtils.closeQuietly(rs);
        }
    }

    /**
     * 猜测主键
     * @param function
     * @param pst
     * @param size
     * @param it
     * @param table
     */
    private void setPkValueAfterFunction(String function, PreparedStatement pst, int size, Iterator<? extends DBObject> it, TableMapping table) {
        ResultSet rs = null;
        PreparedStatement ps = null;
        try {
            ps = pst.getConnection().prepareStatement(function);
            rs = ps.executeQuery();
            if (rs.next()) {
                long max = rs.getLong(1);
                int i = 0;
                while (it.hasNext()) {
                    DBObject o = it.next();
                    ColumnMapping cm = table.getPkFields().get(0);
                    long num = max - (size - (++i));
                    OrmValueUtils.setValue(o, cm, NumberUtils.getTargetNumber(num, cm.getClz()));
                    if (i == size) {
                        break;
                    }
                }
            }
        } catch (Exception e) {
            throw new SmallOrmException(e);
        } finally {
            DBUtils.closeQuietly(rs);
            DBUtils.closeQuietly(ps);
        }
    }

    private void setOracleRowid(PreparedStatement pst, Iterator<? extends DBObject> it) {
        ResultSet rs = null;
        try {
            rs = pst.getGeneratedKeys();
            if (rs == null) {
                throw new SQLException("getGeneratedKeys() returns null from the " + pst + ".");
            }
            while (rs.next()) {
                DBObject obj = it.next();
                String rowid = rs.getString(1);
                obj.bindRowid(rowid);
            }
        } catch (Exception e) {
            throw new SmallOrmException(e);
        } finally {
            DBUtils.closeQuietly(rs);
        }
    }


    @Override
    public boolean createTable(Connection conn, Class<?> clz) {
        TableMapping en = MetaHolder.getMeta(clz);
        DBDialect dialect = DBUtils.doGetDialect(conn, false);
        try {
            String tableName = en.getTableName();
            tableName = dialect.getObjectNameToUse(tableName);
            if (!DBMetaData.getInstance().existTable(conn, tableName)) {
                return dialect.createEntity(conn, en);
            }
        } catch (SQLException e) {
            throw new SmallOrmException(e);
        }
        return false;
    }

    @Override
    public boolean dropTable(Connection conn, Class<?> clz) {
        TableMapping en = MetaHolder.getMeta(clz);
        DBDialect dialect = DBUtils.doGetDialect(conn, false);
        try {
            String tableName = en.getTableName();
            if (DBMS.oracle.getNumber() == dialect.getNumber()) {
                tableName = tableName.toUpperCase();
            }
            if (DBMetaData.getInstance().existTable(conn, tableName)) {
                return dialect.dropEntity(conn, en);
            }
        } catch (SQLException e) {
            throw new SmallOrmException(e);
        }
        return false;
    }

    @Override
    public <T extends DBObject> List<T> fetchCascade(Connection conn, T query, Class<T> clz, DBCascadeField... fields) {
        List<T> list = selectList(conn, query);
        for (T t : list) {
            fetchLinks(conn, t, fields);
        }
        return list;
    }

    @Override
    public <T extends DBObject> T fetchLinks(Connection conn, T obj) {
        Assert.notNull(obj, "Object cannot be null!");
        return fetchLinks(conn, obj, (String[]) null);
    }

    @Override
    public <T extends DBObject> T fetchLinks(Connection conn, T obj, DBCascadeField... fields) {
        Assert.notNull(obj, "Object cannot be null!");
        String[] fed = null;
        if (fields != null) {
            fed = new String[fields.length];
            for (int i = 0; i < fields.length; i++) {
                fed[i] = fields[i].name();
            }
        }
        return fetchLinks(conn, obj, fed);
    }

    @Override
    public <T extends DBObject> T fetchLinks(Connection conn, T obj, String... fields) {
        Assert.notNull(obj, "Object cannot be null!");
        TableMapping table = MetaHolder.getMeta(obj.getClass());
        if (fields != null && fields.length > 0) {
            for (int i = 0; i < fields.length; i++) {
                String field = fields[i];
                ColumnMapping cm = table.getJavaFieldColumnMapping(field);
                if (cm != null && cm.isCascade()) {
                    fetchSubJoin(conn, obj, cm);
                }
            }
        } else {
            List<ColumnMapping> columnMappings = table.getMetaFields();
            for (ColumnMapping cm : columnMappings) {
                if (cm.isCascade()) {
                    fetchSubJoin(conn, obj, cm);
                }
            }
        }
        return obj;
    }

    private <T extends DBObject> void fetchSubJoin(Connection conn, T obj, ColumnMapping cm) {
        fetchSubJoinLink(conn, obj, cm);
    }

    private void fetchSubJoinLink(Connection conn, DBObject obj, ColumnMapping cm) {
        DBDialect dialect = DBUtils.doGetDialect(conn, false);
        CascadeConfig cc = cm.getCascadeConfig();
        if (cc == null) {
            MetaHolder.cascade(MetaHolder.getMeta(obj.getClass()), cm);
            cc = cm.getCascadeConfig();
        }
        CascadeContext select = cc.getSelectSubObject();
        Assert.notNull(select, "Cascade configuration information not found!");
        List<ColumnMapping> mainTableParas = select.getParas();
        SQLContext selectOrm = new SQLContext();
        selectOrm.setSql(select.getSqlByDialect(dialect));
        List<SQLParameter> paras = new ArrayList<>();
        for (ColumnMapping from : mainTableParas) {
            Object value = OrmValueUtils.getValue(obj, from);
            //有值为null,则直接返回,无需再查询.
            if (value == null) {
                return;
            }
            paras.add(new SQLParameter(value, from));
        }
        selectOrm.setParas(paras);
        if (Collection.class.isAssignableFrom(cm.getClz())) {
            //集合
            List subObjs = selectList(conn, cc.getToTable().getThisType(), selectOrm);
            CascadeUtils.setCollectionValues(obj, cm, subObjs);
        } else if (Map.class.isAssignableFrom(cm.getClz())) {
            List subObjs = selectList(conn, cc.getToTable().getThisType(), selectOrm);
            CascadeUtils.setMapValues(obj, cm, subObjs);
        } else {
            //单一值
            //设置单一值
            Object val = selectOne(conn, cc.getToTable().getThisType(), selectOrm);
            OrmValueUtils.setValue(obj, cm, val);
        }
    }

    @Override
    public <T extends DBObject> T insertLinks(Connection con, T obj, DBCascadeField... fields) {
        Assert.notNull(obj, "Object cannot be null!");
        TableMapping table = MetaHolder.getMeta(obj.getClass());
        int count = 0;
        if (fields != null && fields.length > 0) {
            for (int i = 0; i < fields.length; i++) {
                DBCascadeField field = fields[i];
                ColumnMapping cm = table.getJavaFieldColumnMapping(field.name());
                if (cm != null && cm.isCascade()) {
                    count += insertLink(con, obj, cm);
                }
            }
        } else {
            List<ColumnMapping> columnMappings = table.getMetaFields();
            for (ColumnMapping cm : columnMappings) {
                if (cm.isCascade()) {
                    count += insertLink(con, obj, cm);
                }
            }
        }
        return obj;
    }

    private <T extends DBObject> int insertLink(Connection con, T obj, ColumnMapping cm) {
        int count = 0;
        CascadeConfig cc = cm.getCascadeConfig();
        if (cc != null) {
            //插入子对象
            count = insertSubObject(con, obj, cm);
            if (cc.getInsertRelation() != null) {
                //插入关联关系表,对应多对多
                insertRelation(con, obj, cm.getCascadeField());
            } else if (cc.getUpdateRelation() != null) {
                Object subObject = OrmValueUtils.getValue(obj, cm);
                updateRelation(con, obj, cm, subObject);
            }
        }
        return count;
    }

    private int insertSubObject(Connection conn, DBObject obj, ColumnMapping cm) {
        Object subObject = OrmValueUtils.getValue(obj, cm);
        CascadeConfig cc = cm.getCascadeConfig();
        Map<ColumnMapping, ColumnMapping> fromToColumns = null;
        if (cc != null && !cc.isUseMappedBy()) {
            fromToColumns = cc.getFromToColumns();
        }
        int count = 0;
        if (subObject != null) {
            if (Collection.class.isAssignableFrom(subObject.getClass())) {
                Collection collection = (Collection) subObject;
                for (Object sub : collection) {
                    setSubJoinColumnValue(obj, fromToColumns, sub);
                    count += insert(conn, (DBObject) sub);
                }
            } else if (Map.class.isAssignableFrom(subObject.getClass())) {
                //不支持map
                logger.warn(" not support insert sub object by map");
            } else {
                setSubJoinColumnValue(obj, fromToColumns, subObject);
                count += insert(conn, (DBObject) subObject);
            }
        }
        return count;
    }

    /**
     * 设置子对象的关联字段值
     * @param obj
     * @param fromToColumns
     * @param sub
     */
    private void setSubJoinColumnValue(DBObject obj, Map<ColumnMapping, ColumnMapping> fromToColumns, Object sub) {
        if (fromToColumns != null) {
            for (Map.Entry<ColumnMapping, ColumnMapping> entry : fromToColumns.entrySet()) {
                Object joinColumnValue = OrmValueUtils.getValue(obj, entry.getKey());
                OrmValueUtils.setValue(sub, entry.getValue(), joinColumnValue);
            }
        }
    }

    @Override
    public <T extends DBObject> T insertRelation(Connection con, T obj, DBCascadeField... fields) {
        Assert.notNull(obj, "Object cannot be null!");
        Assert.notNull(fields, "fields is null!");
        TableMapping table = MetaHolder.getMeta(obj.getClass());
        for (int i = 0; i < fields.length; i++) {
            DBCascadeField field = fields[i];
            ColumnMapping cm = table.getJavaFieldColumnMapping(field.name());
            if (cm != null && cm.isCascade()) {
                insertRelation(con, obj, cm);
            } else {
                throw new RuntimeException("field not found!");
            }
        }
        return obj;
    }

    private int insertRelation(Connection conn, DBObject obj, ColumnMapping cm) {
        DBDialect dialect = DBUtils.doGetDialect(conn, false);
        int count = 0;
        CascadeConfig cc = cm.getCascadeConfig();
        if (cc == null) {
            MetaHolder.cascade(MetaHolder.getMeta(obj.getClass()), cm);
            cc = cm.getCascadeConfig();
        }
        Object subObject = OrmValueUtils.getValue(obj, cm);
        //为空或者不是集合对象
        if (subObject == null || !Collection.class.isAssignableFrom(subObject.getClass())) {
            return count;
        }
        Collection<?> subObjects = (Collection<?>) subObject;
        CascadeContext insert = cc.getInsertRelation();
        String sql = insert.getSqlByDialect(dialect);
        List<ColumnMapping> mainTableParas = insert.getParas();
        //设置关系表的值,几个子对象,设置几个子对象的关系.
        List<Object[]> relationsValues = new ArrayList<>();
        for (int i = 0; i < subObjects.size(); i++) {
            Object[] relationValues = new Object[cc.getMiddleTableColumns().size()];
            relationsValues.add(relationValues);
        }
        int i = 0;
        for (ColumnMapping column : mainTableParas) {
            if (column.getMeta().getThisType() == cc.getFromTable().getThisType()) {
                Object value = OrmValueUtils.getValue(obj, column);
                for (int j = 0; j < subObjects.size(); j++) {
                    relationsValues.get(j)[i] = value;
                }
            } else if (column.getMeta().getThisType() == cc.getToTable().getThisType()) {
                int k = 0;
                for (Object sub : subObjects) {
                    Object subValue = OrmValueUtils.getValue(sub, column);
                    relationsValues.get(k)[i] = subValue;
                    k++;
                }
            }
            i++;
        }
        if (!relationsValues.isEmpty()) {
            int[] all = Crud.getInstance().getCrudSql().executeBatch(conn, sql, relationsValues);
            if (all != null) {
                for (int j = 0; j < all.length; j++) {
                    count += all[j];
                }
            }
        } else {
            logger.warn("insert sub object relation is empty");
        }
        return count;
    }

    @Override
    public <T extends DBObject> int updateLinks(Connection con, T obj, DBCascadeField... fields) {
        Assert.notNull(obj, "Object cannot be null!");
        TableMapping table = MetaHolder.getMeta(obj.getClass());
        List<ColumnMapping> columnMappings = table.getMetaFields();
        int count = 0;
        if (fields != null && fields.length > 0) {
            for (int i = 0; i < fields.length; i++) {
                DBCascadeField field = fields[i];
                ColumnMapping cm = table.getJavaFieldColumnMapping(field.name());
                if (cm != null && cm.isCascade()) {
                    count += updateLink(con, obj, cm);
                }
            }
        } else {
            for (ColumnMapping cm : columnMappings) {
                if (cm.isCascade()) {
                    count += updateLink(con, obj, cm);
                }
            }
        }
        return count;
    }

    private <T extends DBObject> int updateLink(Connection con, T obj, ColumnMapping cm) {
        int count = 0;
        CascadeConfig cc = cm.getCascadeConfig();
        if (cc != null) {
            //更新子对象
            count = updateSubObject(con, obj, cm);
            //可以判断是JoinTable关系
            if (cc.getInsertRelation() != null) {
                //更新关联关系
                updateRelation(con, obj, cm.getCascadeField());
            }
        }
        return count;
    }

    /**
     * @param conn
     * @param obj
     * @param cm
     * @return 更新对象
     */
    private int updateSubObject(Connection conn, DBObject obj, ColumnMapping cm) {
        Object subObject = OrmValueUtils.getValue(obj, cm);
        CascadeConfig cc = cm.getCascadeConfig();
        Map<ColumnMapping, ColumnMapping> fromToColumns = null;
        if (cc != null && !cc.isUseMappedBy()) {
            fromToColumns = cc.getFromToColumns();
        }
        int count = 0;
        if (subObject != null) {
            if (Collection.class.isAssignableFrom(subObject.getClass())) {
                Collection<?> collection = (Collection<?>) subObject;
                for (Object sub : collection) {
                    setSubJoinColumnValue(obj, fromToColumns, sub);
                    DBObject subObj = (DBObject) sub;
                    if (!subObj.updateValueMap().isEmpty()) {
                        count += update(conn, subObj);
                    }
                }
            } else if (Map.class.isAssignableFrom(subObject.getClass())) {
                //不支持map
                logger.warn(" not support update sub object by map");
            } else {
                setSubJoinColumnValue(obj, fromToColumns, subObject);
                DBObject subObj = (DBObject) subObject;
                if (!subObj.updateValueMap().isEmpty()) {
                    count += update(conn, subObj);
                }
            }
        }
        return count;
    }

    @Override
    public <T extends DBObject> int updateRelation(Connection con, T obj, DBCascadeField... fields) {
        Assert.notNull(obj, "Object cannot be null!");
        Assert.notNull(fields, "Field cannot be null!");
        TableMapping table = MetaHolder.getMeta(obj.getClass());
        int count = 0;
        for (int i = 0; i < fields.length; i++) {
            DBCascadeField field = fields[i];
            ColumnMapping cm = table.getJavaFieldColumnMapping(field.name());
            if (cm != null && cm.isCascade()) {
                Object subObject = OrmValueUtils.getValue(obj, cm);
                count += updateRelation(con, obj, cm, subObject);
            } else {
                throw new RuntimeException("field not found!");
            }
        }
        return count;
    }

    /**
     * @param con
     * @param obj       主对象
     * @param cm        obj的级联字段
     * @param subObject 子对象
     * @param <T>
     * @return 更新的关系数量
     */
    private <T extends DBObject> int updateRelation(Connection con, T obj, ColumnMapping cm, Object subObject) {
        Assert.notNull(obj, "Object cannot be null!");
        Assert.notNull(cm, "Field cannot be null!");
        CascadeConfig cc = cm.getCascadeConfig();
        if (cc == null) {
            MetaHolder.cascade(MetaHolder.getMeta(obj.getClass()), cm);
            cc = cm.getCascadeConfig();
        }
        int count = 0;
        DBDialect dialect = DBUtils.doGetDialect(con, false);
        if (cc.getType() == CascadeConfig.LinkType.JoinColumns) {
            CascadeContext context = cc.getUpdateRelation();
            if (context != null) {
                List<Object[]> relationValues = new ArrayList<>();
                if (Collection.class.isAssignableFrom(subObject.getClass())) {
                    Collection subObjects = (Collection) subObject;
                    for (Object sub : subObjects) {
                        List<Object> rv = new ArrayList<>();
                        for (ColumnMapping co : context.getParas()) {
                            if (co.getMeta().getThisType() == obj.getClass()) {
                                rv.add(OrmValueUtils.getValue(obj, co));
                            } else if (co.getMeta().getThisType() == sub.getClass()) {
                                rv.add(OrmValueUtils.getValue(sub, co));
                            }
                        }
                        relationValues.add(rv.toArray());
                    }
                } else if (Map.class.isAssignableFrom(subObject.getClass())) {
                    //不支持map
                    logger.warn(" not support update sub object by map");
                } else {
                    List<Object> rv = new ArrayList<>();
                    for (ColumnMapping co : context.getParas()) {
                        if (co.getMeta().getThisType() == obj.getClass()) {
                            rv.add(OrmValueUtils.getValue(obj, co));
                        } else if (co.getMeta().getThisType() == subObject.getClass()) {
                            rv.add(OrmValueUtils.getValue(subObject, co));
                        }
                    }
                    relationValues.add(rv.toArray());
                }
                if (!relationValues.isEmpty()) {
                    Crud.getInstance().getCrudSql().executeBatch(con, context.getSqlByDialect(dialect), relationValues);
                } else {
                    logger.warn("update sub object relation is empty");
                }
            }
        } else if (cc.getType() == CascadeConfig.LinkType.JoinTable) {
            if (subObject != null) {
                deleteRelation(con, obj, cm);
                count += insertRelation(con, obj, cm);
            }
        }
        return count;
    }

    /**
     * @param con
     * @param obj    数据对象
     * @param fields 正则表达式，描述了什么样的关联字段将被关注。如果为 null，则表示全部的关联字段都会被删除
     * @param <T>
     * @return 删除的关系数量
     */
    @Override
    public <T extends DBObject> int deleteLinks(Connection con, T obj, DBCascadeField... fields) {
        Assert.notNull(obj, "Object cannot be null!");
        TableMapping table = MetaHolder.getMeta(obj.getClass());
        List<ColumnMapping> columnMappings = table.getMetaFields();
        int count = 0;
        if (fields != null && fields.length > 0) {
            for (int i = 0; i < fields.length; i++) {
                DBCascadeField field = fields[i];
                ColumnMapping cm = table.getJavaFieldColumnMapping(field.name());
                if (cm != null && cm.isCascade()) {
                    count += deleteLink(con, obj, cm);
                }
            }
        } else {
            for (ColumnMapping cm : columnMappings) {
                if (cm.isCascade()) {
                    count += deleteLink(con, obj, cm);
                }
            }
        }
        return count;
    }

    private <T extends DBObject> int deleteLink(Connection con, T obj, ColumnMapping cm) {
        int count = 0;
        CascadeConfig cc = cm.getCascadeConfig();
        if (cc != null && cc.getDeleteSubObject() != null) {
            //删除子对象
            count = deleteSubObject(con, obj, cm);
            if (cc.getDeleteRelation() != null) {
                //删除关联关系
                count += deleteRelation(con, obj, cm);
            }
        }
        return count;
    }

    private int deleteSubObject(Connection conn, DBObject obj, ColumnMapping cm) {
        CascadeConfig cc = cm.getCascadeConfig();
        if (cc == null) {
            MetaHolder.cascade(MetaHolder.getMeta(obj.getClass()), cm);
            cc = cm.getCascadeConfig();
        }
        DBDialect dialect = DBUtils.doGetDialect(conn, false);
        CascadeContext delete = cc.getDeleteSubObject();
        String sql = delete.getSqlByDialect(dialect);
        List<ColumnMapping> mainTableParas = delete.getParas();
        //设置主表的值
        List<Object> relationValues = new ArrayList<>();
        for (ColumnMapping column : mainTableParas) {
            Object value = OrmValueUtils.getValue(obj, column);
            relationValues.add(value);
        }
        return Crud.getInstance().getCrudSql().execute(conn, sql, relationValues.toArray());
    }

    /**
     * 多对多
     * @param con
     * @param obj
     * @param fields 正则表达式，描述了那种多对多关联字段将被执行该操作
     * @param <T>
     * @return 删除的关系数量
     */
    @Override
    public <T extends DBObject> int deleteRelation(Connection con, T obj, DBCascadeField... fields) {
        Assert.notNull(obj, "Object cannot be null!");
        Assert.notNull(fields, "Field cannot be null!");
        TableMapping table = MetaHolder.getMeta(obj.getClass());
        int count = 0;
        for (int i = 0; i < fields.length; i++) {
            DBCascadeField field = fields[i];
            ColumnMapping cm = table.getJavaFieldColumnMapping(field.name());
            if (cm != null && cm.isCascade()) {
                count += deleteRelation(con, obj, cm);
            } else {
                throw new RuntimeException("field not found!");
            }
        }
        return count;
    }

    private int deleteRelation(Connection conn, DBObject obj, ColumnMapping cm) {
        CascadeConfig cc = cm.getCascadeConfig();
        if (cc == null) {
            MetaHolder.cascade(MetaHolder.getMeta(obj.getClass()), cm);
            cc = cm.getCascadeConfig();
        }
        CascadeContext delete = cc.getDeleteRelation();
        DBDialect dialect = DBUtils.doGetDialect(conn, false);
        String sql = delete.getSqlByDialect(dialect);
        List<ColumnMapping> mainTableParas = delete.getParas();
        //设置主表的值
        List<Object> relationValues = new ArrayList<>();
        for (ColumnMapping column : mainTableParas) {
            Object value = OrmValueUtils.getValue(obj, column);
            relationValues.add(value);
        }
        return Crud.getInstance().getCrudSql().execute(conn, sql, relationValues.toArray());
    }
}
